﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;
namespace QuanLySieuThi_08520114_08520220
{
    class controller_mathang
    {
        private SqlCeDataAdapter temp_adapter;
        private DataTable temp_table;
        private SqlCeConnection con = null;

        /*===================  Chung Loai ====================*/
        public DataTable list_chung_loai()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From CHUNGLOAI";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool del_chung_loai(string ma_chung_loai)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From CHUNGLOAI Where MaChungLoai = '" + ma_chung_loai + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_chung_loai(string ten_chung_loai)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From CHUNGLOAI Where TenChungLoai ='" + ten_chung_loai + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên Chủng Loại đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into CHUNGLOAI (TenChungLoai) VALUES ('" + ten_chung_loai + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;        
        }

        public bool update_chung_loai(string ma_chung_loai,string ten_chung_loai)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From CHUNGLOAI Where TenChungLoai ='" + ten_chung_loai + "' and MaChungLoai != '" + ma_chung_loai + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên Chủng Loại đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update CHUNGLOAI Set TenChungLoai = '" + ten_chung_loai + "' Where MaChungLoai = '" + ma_chung_loai + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        /*============================================================*/


        /*===================  Đơn vị tính ====================*/
        public DataTable list_DVT()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From DONVITINH";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool del_DVT(string ma_dvt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From DONVITINH Where MaDonViTinh = '" + ma_dvt + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_DVT(string ten_dvt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From DONVITINH Where TenDonViTinh ='" + ten_dvt + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên Đơn Vị Tính đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into DONVITINH (TenDonViTinh) VALUES ('" + ten_dvt + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool update_DVT(string ma_dvt, string ten_dvt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From DONVITINH Where TenDonViTinh ='" + ten_dvt + "' and MaDonViTinh != '" + ma_dvt + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên Đơn Vị Tính đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update DONVITINH Set TenDonViTinh = '" + ten_dvt + "' Where MaDonViTinh = '" + ma_dvt + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        /*============================================================*/

        /*===================  Mặt hàng  ====================*/
        public string get_ma_cl(string ten_cl)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";


                    sql = "Select MaChungLoai From CHUNGLOAI where TenChungLoai = '" + ten_cl +"'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    string ma = Convert.ToString(cmd.ExecuteScalar());
                    ToanCuc.disconnect(ref con);
                    return ma;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public string get_ma_dvt(string ten_dvt)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";


                    sql = "Select MaDonViTinh From DONVITINH where TenDonViTinh = '" + ten_dvt +"'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    string ma = Convert.ToString(cmd.ExecuteScalar());
                    ToanCuc.disconnect(ref con);
                    return ma;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable list_mathang()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    
                    sql = "Select * From MATHANG m LEFT JOIN CHUNGLOAI c on m.MaChungLoai = c.MaChungLoai Left join DONVITINH d on m.MaDonViTinh = d.MaDonViTinh";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool del_mathang(string ma_mh)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From MATHANG Where MatHangID = '" + ma_mh + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_mathang(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From MATHANG Where TenMatHang ='" + data[3] + "' or MaMatHang ='" + data[0] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã hoặc Tên Mặt Hàng đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into MATHANG (MaMatHang,MaChungLoai,MaDonViTinh,TenMatHang,NhaSanXuat,NhaPhanPhoi,GiaNhap,GiaBanThuc,KhuyenMai,GiaTruocKhuyenMai) VALUES ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "','" + data[8] + "','" + data[9] + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool update_mathang(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From MATHANG Where (TenMatHang ='" + data[3] + "' or MaMatHang ='" + data[0] + "') and MatHangID != '" + data[10] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã hoặc Tên Mặt Hàng đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update MatHang Set MaMatHang = '" + data[0] + "',MaChungLoai = '" + data[1] + "',MaDonViTinh = '" + data[2] + "',TenMatHang = '" + data[3] + "',NhaSanXuat = '" + data[4] + "',NhaPhanPhoi = '" + data[5] + "',GiaNhap = '" + data[6] + "',GiaBanThuc = '" + data[7] + "',KhuyenMai = '" + data[8] + "',GiaTruocKhuyenMai = '" + data[9] + "' Where MatHangID = '" + data[10] + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        /*============================================================*/
    }
}
